USE [arms]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/****************************************************************************************************************/
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'bookingview')
DROP VIEW [dbo].[bookingview]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'workersview')
DROP VIEW [dbo].[workersview]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'shopinventoryview')
DROP VIEW [dbo].[shopinventoryview]
GO

/****************************************************************************************************************/
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_workorder_booking') 
ALTER TABLE [dbo].[pricelist] DROP CONSTRAINT [fk_pricelist_shopinventory]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_workorder_booking') 
ALTER TABLE [dbo].[workorder] DROP CONSTRAINT [fk_workorder_booking]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_workorder_customer') 
ALTER TABLE [dbo].[workorder] DROP CONSTRAINT [fk_workorder_customer]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_workorder_worker') 
ALTER TABLE [dbo].[workorder] DROP CONSTRAINT [fk_workorder_worker]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_partorder_workorder') 
ALTER TABLE [dbo].[partorder] DROP CONSTRAINT [fk_partorder_workorder]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_booking_customer') 
ALTER TABLE [dbo].[booking] DROP CONSTRAINT [fk_booking_customer]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_shopinventory_shop') 
ALTER TABLE [dbo].[shopinventory] DROP CONSTRAINT [fk_shopinventory_shop]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_partorder_workorder') 
ALTER TABLE [dbo].[partorder] DROP CONSTRAINT [fk_partorder_workorder]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_booking_service') 
ALTER TABLE [dbo].[booking] DROP CONSTRAINT [fk_booking_service]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_booking_worker') 
ALTER TABLE [dbo].[booking] DROP CONSTRAINT [fk_booking_worker]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='ux_worker_unique') 
ALTER TABLE [dbo].[worker] DROP CONSTRAINT [ux_worker_unique]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_worker_speciality') 
ALTER TABLE [dbo].[worker] DROP CONSTRAINT [fk_worker_speciality]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_workorder_booking') 
ALTER TABLE [dbo].[workorder] DROP CONSTRAINT [fk_workorder_booking]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_shopinventory_inventory') 
ALTER TABLE [dbo].[shopinventory] DROP CONSTRAINT [fk_shopinventory_inventory]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_shopinventory_shop') 
ALTER TABLE [dbo].[shopinventory] DROP CONSTRAINT [fk_shopinventory_shop]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_partorder_partid') 
ALTER TABLE [dbo].[partorder] DROP CONSTRAINT [fk_partorder_partid]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_shopinventory_shop') 
ALTER TABLE [dbo].[services] DROP CONSTRAINT [fk_shopinventory_shop]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='fk_shopinventory_shop') 
ALTER TABLE [dbo].[partorder] DROP CONSTRAINT [fk_shopinventory_shop]
GO

/****************************************************************************************************************/
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'pricelist')
DROP TABLE [dbo].[pricelist]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'shopinventory')
DROP TABLE [dbo].[shopinventory]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'partorder')
DROP TABLE [dbo].[partorder]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'inventory')
DROP TABLE [dbo].[inventory]  
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'workorder')
DROP TABLE [dbo].[workorder]

IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'booking')
DROP TABLE [dbo].[booking]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'customer')
DROP TABLE [dbo].[customer]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'worker')
DROP TABLE [dbo].[worker]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'speciality')
DROP TABLE [dbo].[speciality]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'shop')
DROP TABLE [dbo].[shop]
GO
IF EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user')
DROP TABLE [dbo].[user]
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[user](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL,
	[username] [varchar](30) NOT NULL,
	[password] [varchar](30) NOT NULL,
	[active] [bit] NOT NULL,
CONSTRAINT [pk_account] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_user_firstname] ON [dbo].[user]
(
	[firstname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_user_lastname] ON [dbo].[user]
(
	[lastname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ix_user_unique] ON [dbo].[user]
(
	[username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[shop](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](100) NOT NULL,
	[state] [varchar](25) NOT NULL,
	[address] [varchar](150) NOT NULL,
	[code] [varchar](10) NULL,
 CONSTRAINT [PK_shop] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [ux_shop_unique] ON [dbo].[shop]
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[speciality] (
	[id] [int] NOT NULL,
	[description] [varchar](20) NOT NULL
	CONSTRAINT [pk_speciality] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[inventory](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[description] [varchar](50) NOT NULL,
	[service] [bit] NOT NULL,
 CONSTRAINT [pk_inventory] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****************************************************************************************************************/
CREATE TABLE [dbo].[worker](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[userid] [int] NOT NULL,
	[roleid] [int] NOT NULL,
	[shopid] [int] NOT NULL,
	[active] [bit] NOT NULL
 CONSTRAINT [pk_worker] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ux_worker_unique] ON [dbo].[worker]
(
	[userid] ASC,
	[roleid] ASC,
	[shopid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[worker]  WITH CHECK ADD CONSTRAINT [fk_worker_speciality] FOREIGN KEY([roleid]) REFERENCES [dbo].[speciality] ([id])
GO
ALTER TABLE [dbo].[worker] CHECK CONSTRAINT [fk_worker_speciality]

/****************************************************************************************************************/
CREATE TABLE [dbo].[customer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL,
	[address] [varchar](200) NULL,
	[mobile] [varchar](15) NULL,
	[telephone] [varchar](15) NULL,
	[email] [varchar](32) NULL,
	[memberno] [varchar](10) NULL,
	[model] [varchar] (20) NULL,
	[shopid] [int] NULL
 CONSTRAINT [pk_customer] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_customer_firstname] ON [dbo].[customer]
(
	[firstname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_customer_lastname] ON [dbo].[customer]
(
	[lastname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[booking](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[customerid] [int] NOT NULL,
	[serviceid] [int] NOT NULL,
	[workerid] [int] NOT NULL,
	[datestamp] [datetime] NOT NULL,
	[note] [text] NOT NULL,
	[active] [bit] NOT NULL,
 CONSTRAINT [pk_booking] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ux_booking_unique] ON [dbo].[booking]
(
	[customerid] ASC,
	[workerid] ASC,
	[datestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[booking]  WITH CHECK ADD CONSTRAINT [fk_booking_service] FOREIGN KEY([serviceid]) REFERENCES [dbo].[inventory] ([id])
GO
ALTER TABLE [dbo].[booking] CHECK CONSTRAINT [fk_booking_service]
GO
ALTER TABLE [dbo].[booking]  WITH CHECK ADD CONSTRAINT [fk_booking_worker] FOREIGN KEY([workerid]) REFERENCES [dbo].[worker] ([id])
GO
ALTER TABLE [dbo].[booking] CHECK CONSTRAINT [fk_booking_worker]
GO
ALTER TABLE [dbo].[booking]  WITH CHECK ADD  CONSTRAINT [fk_booking_customer] FOREIGN KEY([customerid]) REFERENCES [dbo].[customer] ([id])
GO
ALTER TABLE [dbo].[booking] CHECK CONSTRAINT [fk_booking_customer]
GO
/****************************************************************************************************************/
CREATE TABLE [dbo].[workorder](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[bookingid] [int] NOT NULL,
	[customerid] [int] NOT NULL,
	[workerid] [int] NOT NULL,
	[worknote] [nvarchar](MAX) NOT NULL,
	[status] [nvarchar](20) NOT NULL,
 CONSTRAINT [pk_workorder] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ux_workorder_unique] ON [dbo].[workorder]
(
	[id] ASC,
	[bookingid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[workorder]  WITH CHECK ADD CONSTRAINT [fk_workorder_booking] FOREIGN KEY([bookingid]) REFERENCES [dbo].[booking] ([id])
GO
ALTER TABLE [dbo].[workorder] CHECK CONSTRAINT [fk_workorder_booking]
GO
ALTER TABLE [dbo].[workorder]   WITH CHECK ADD  CONSTRAINT [fk_workorder_customer] FOREIGN KEY([customerid]) REFERENCES [dbo].[customer] ([id])
GO
ALTER TABLE [dbo].[workorder]  CHECK CONSTRAINT [fk_workorder_customer]
GO
ALTER TABLE [dbo].[workorder]   WITH CHECK ADD  CONSTRAINT [fk_workorder_worker] FOREIGN KEY([workerid]) REFERENCES [dbo].[worker] ([id])
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[shopinventory](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[shopid] [int] NOT NULL,
	[inventoryid] [int] NOT NULL,
	[active] [bit] NOT NULL,
 CONSTRAINT [pk_shopinventory] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[shopinventory]  WITH CHECK ADD  CONSTRAINT [fk_shopinventory_inventory] FOREIGN KEY([inventoryid])
REFERENCES [dbo].[inventory] ([id])
GO
ALTER TABLE [dbo].[shopinventory] CHECK CONSTRAINT [fk_shopinventory_inventory]
GO
ALTER TABLE [dbo].[shopinventory]  WITH CHECK ADD  CONSTRAINT [fk_shopinventory_shop] FOREIGN KEY([shopid])
REFERENCES [dbo].[shop] ([id])
GO
ALTER TABLE [dbo].[shopinventory] CHECK CONSTRAINT [fk_shopinventory_shop]
GO
/****************************************************************************************************************/
CREATE TABLE [dbo].[pricelist](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[shopinventoryid] [int] NOT NULL,
	[price] [numeric] (15,2) NOT NULL,
	[datestamp] [datetime] NOT NULL DEFAULT getdate(),
	CONSTRAINT [pk_pricelist] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[pricelist]  WITH CHECK ADD  CONSTRAINT [fk_pricelist_shopinventory] FOREIGN KEY([shopinventoryid])
REFERENCES [dbo].[shopinventory] ([id])
GO

ALTER TABLE [dbo].[pricelist] CHECK CONSTRAINT [fk_pricelist_shopinventory]
GO

/****************************************************************************************************************/
CREATE TABLE [dbo].[partorder](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[workorderid] [int] NOT NULL,
	[partid] [int] NOT NULL,
	[quantity] [int] NOT NULL,
 CONSTRAINT [pk_partorder] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ux_partorder_unique] ON [dbo].[partorder]
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[partorder]  WITH CHECK ADD CONSTRAINT [fk_partorder_workorder] FOREIGN KEY([workorderid]) REFERENCES [dbo].[workorder] ([id])
GO
ALTER TABLE [dbo].[partorder] CHECK CONSTRAINT [fk_partorder_workorder]
GO
ALTER TABLE [dbo].[partorder]   WITH CHECK ADD  CONSTRAINT [fk_partorder_partid] FOREIGN KEY([partid]) REFERENCES [dbo].[inventory] ([id])
GO
ALTER TABLE [dbo].[partorder]  CHECK CONSTRAINT [fk_partorder_partid]
GO


/****************************************************************************************************************/
CREATE VIEW [dbo].[bookingview]
AS
SELECT booking.id, 
       booking.customerid,
	   booking.serviceid,
	   [service].[description] AS servicename,
       booking.workerid, 
	   booking.datestamp, 
	   booking.note, 
	   booking.active, 
	   customer.memberno,
       customer.firstname +' '+ customer.lastname AS customername, 
	   usr.firstname +' '+ usr.lastname AS workername,
	   worker.shopid AS shopid
FROM   dbo.booking as booking
LEFT OUTER JOIN dbo.inventory AS [service] ON [service].id = booking.serviceid
LEFT OUTER JOIN dbo.customer AS customer ON customer.id = booking.customerid
LEFT OUTER JOIN dbo.worker AS worker ON worker.id = booking.workerid
LEFT OUTER JOIN dbo.[user] AS usr ON worker.userid = usr.id
GO

CREATE VIEW [dbo].[workersview]
AS
SELECT  usr.id, 
		usr.firstname, 
		usr.lastname, 
		usr.username, 
		worker.id AS workerid,
		worker.userid, 
		worker.roleid,
		spec.[description] AS speciality, 
        worker.shopid, 
		worker.active
FROM  dbo.worker AS worker
LEFT OUTER JOIN dbo.[user] AS usr ON worker.userid = usr.id
LEFT OUTER JOIN dbo.speciality AS spec ON worker.roleid = spec.id
GO

CREATE VIEW [dbo].[shopinventoryview]
AS
SELECT 
	inventory.id, 
	inventory.[description], 
	inventory.[service], 
	shopinventory.shopid, 
	shopinventory.active
FROM dbo.inventory AS inventory
	LEFT OUTER JOIN dbo.shopinventory AS shopinventory ON inventory.id = shopinventory.inventoryid
WHERE 
	shopinventory.active = 1
GO


/****************************************************************************************************************/
INSERT INTO [dbo].[shop] ([name],[state],[address],[code]) VALUES ('Canberra', 'ACT', '1 Avenue Name, 2612', 'CanACT01')
GO
INSERT INTO [dbo].[shop] ([name],[state],[address],[code]) VALUES ('Sydney', 'NSW', '1 Military Road', '')
GO
INSERT INTO [dbo].[shop] ([name],[state],[address],[code]) VALUES ('Perth', 'WA', '1 Swan Road', '')
GO


INSERT INTO [dbo].[user] ([firstname], [lastname], [username], [password], [active])
     VALUES ('John', 'Admin', 'admin', ' ', 1)
GO
INSERT INTO [dbo].[user] ([firstname], [lastname], [username], [password], [active])
     VALUES ('Lucy','Lui','lucy','xxxx',1)
GO
INSERT INTO [dbo].[user] ([firstname], [lastname], [username], [password], [active])
     VALUES ('Jena','Receptionist', 'receptionist', ' ', 1)
GO
INSERT INTO [dbo].[user] ([firstname],[lastname],[username],[password],[active])
     VALUES ('Joe','Mechanic','joe','xxxx',1)
GO
INSERT INTO [dbo].[user]([firstname],[lastname],[username],[password],[active])
     VALUES ('Steve','Manager','steve','xxxx',1)
GO


INSERT INTO [dbo].[customer] ([firstname],[lastname],[address],[mobile],[telephone],[email],[memberno],[shopid], [model]) VALUES
           ('John','Doe','1 Street Name, 10000 City, Country','+61400200399' ,'+61400200399','johndoe@gmail.com','',1, 'Honda Civic')
GO
INSERT INTO [dbo].[customer] ([firstname],[lastname],[address],[mobile],[telephone],[email],[memberno],[shopid]) VALUES
           ('Clark','Gable','1 Street Name, 10000 City, Country','+61298239494','+61400200399','','1JD0001',1)
GO
INSERT INTO [dbo].[customer] ([firstname],[lastname],[address],[mobile],[telephone],[email],[memberno],[shopid]) VALUES
           ('Nicole','Doe','1 Street Name, 10000 City, Country','+61298239494','+61400200399','janedoe@gmail.com','',1)
GO
INSERT INTO [dbo].[customer] ([firstname],[lastname],[address],[mobile],[telephone],[email],[memberno],[shopid]) VALUES
           ('Robin','Doe','1 Street Name, 10000 City, Country','+61298239494','+61400200399','janedoe@gmail.com','',1)
GO
INSERT INTO [dbo].[customer] ([firstname],[lastname],[address],[mobile],[telephone],[email],[memberno],[shopid]) VALUES
           ('Bryan','James','1 Street Name, 10000 City, Country','+61298239494','+61400200399','janedoe@gmail.com','',1)
GO
/*
 >>>WorkersModel

        All = 0x0,
        Electrician = 0x1,
        Mechanic = 0x2,
        Other = 0x3
*/
INSERT INTO [dbo].[speciality] ([id],[description]) VALUES(1, 'Electrician')
GO
INSERT INTO [dbo].[speciality] ([id],[description]) VALUES(2, 'Mechanic')
GO
INSERT INTO [dbo].[speciality] ([id],[description]) VALUES(3, 'Other')
GO


INSERT INTO [dbo].[worker] ([userid],[roleid],[shopid],[active]) VALUES(1, 1, 1, 1)
GO
INSERT INTO [dbo].[worker] ([userid],[roleid],[shopid],[active]) VALUES(2, 3, 2, 1)
GO
INSERT INTO [dbo].[worker] ([userid],[roleid],[shopid],[active]) VALUES(4, 1, 1, 1)
GO
INSERT INTO [dbo].[worker] ([userid],[roleid],[shopid],[active]) VALUES(4, 2, 1, 1)
GO
INSERT INTO [dbo].[worker] ([userid],[roleid],[shopid],[active]) VALUES(5, 2, 1, 1)
GO


INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('General service for Holden cars', 1)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('Winter tyre change', 1)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('Towing', 1)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('Electrical diagnostic', 1)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('Mechanical diagnostic', 0)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('Holden Commodore 2008 Carburator', 0)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('Holden Colorado 2012 Clutch', 0)
GO
INSERT INTO [dbo].[inventory] ([description],[service]) VALUES('4x Tyres 165x17', 0)
GO


INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(1, 3, 6, '2012-04-20 09:00:00','Holden Commodore carburator problem', 0)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(2, 2, 1, '2013-05-20 09:30:00','General service for Falcon 2000' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(4, 3, 4, '2013-05-21 13:00:00','Electric inspection on car' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(3, 1, 5, '2013-05-21 14:00:00','Coooler on Mazda and starting problems' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(5, 4, 4, '2013-05-21 15:00:00','Battery replacement for Holden Astra' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(2, 1, 4, '2013-05-22 12:00:00','Ford Falcon with starting problems' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(3, 3, 5, '2013-05-23 09:00:00','2004 car mechanical problems' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(4, 2, 4, '2013-05-23 10:00:00','Mazda with starting problems' ,1)
GO
INSERT INTO [dbo].[booking] (customerid, workerid, serviceid, datestamp, note, active) VALUES(2, 4, 3, '2013-05-24 11:00:00','Falcon need a towing' ,1)
GO


INSERT INTO [dbo].[workorder] VALUES(1, 1, 1, 'Possibly needs new radiator' ,'Scheduled')
GO
INSERT INTO [dbo].[workorder] VALUES(2, 2, 1, '', 'Scheduled')
GO


INSERT INTO [dbo].[shopinventory] VALUES(1, 1, 1)
GO
INSERT INTO [dbo].[shopinventory] VALUES(1, 2, 1)
GO
INSERT INTO [dbo].[shopinventory] VALUES(1, 3, 1)
GO
INSERT INTO [dbo].[shopinventory] VALUES(1, 4, 1)
GO
